#/bin/bash

charset='--default-character-set=utf8'
db='monitor'
host='10.61.13.97'
tableSuffix='_20131227'
programFile='program.csv'

i=0
while read line
do
    id=`echo $line | cut -d' ' -f1`
    name=`echo $line | cut -d' ' -f2`
    pidArr[i]=$id
    pNameArr[i]=$name
    i=$((i+1))
done < $programFile

size=${#pidArr[@]}
for ((i=0; i<$size; i++))
do
  echo "[info] calculating for ${pNameArr[$i]} : ${pidArr[$i]}...."
  pid=${pidArr[$i]}
  mysql -uroot -pmysql -h$host $charset $db -t -e"
  select t1.*, t2.order_count_bmm, t2.order_count_bmm/t1.order_count_total from 
    (select date_format(subscribetime,'%Y-%m-%d') day, count(*) order_count_total from order_ppv$tableSuffix where mediacode='$pid' group by date_format(subscribetime, '%Y-%m-%d')) t1 left join
    (select date_format(subscribetime,'%Y-%m-%d') day, count(distinct o.orderid) order_count_bmm from order_ppv$tableSuffix o, newclicklog$tableSuffix c where o.mediacode='$pid' and c.toprogram='$pid' and o.userid=c.userid and abs(timestampdiff(SECOND, c.clicktime, o.subscribetime))<=3600*24 group by date_format(o.subscribetime, '%Y-%m-%d')) t2
  on t1.day=t2.day; "
done

echo "[info] total result..."
mysql -uroot -pmysql -h$host $charset $db -t -e"
 select t1.*, t2.order_count_bmm, t2.order_count_bmm/t1.order_count_total from 
    (select date_format(subscribetime,'%Y-%m-%d') day, count(*) order_count_total from order_ppv$tableSuffix group by date_format(subscribetime, '%Y-%m-%d')) t1 left join
    (select date_format(subscribetime,'%Y-%m-%d') day, count(distinct o.orderid) order_count_bmm from order_ppv$tableSuffix o, newclicklog$tableSuffix c where o.mediacode=c.toprogram and o.userid=c.userid and timestampdiff(SECOND, c.clicktime, o.subscribetime)<=3600*24 group by date_format(o.subscribetime, '%Y-%m-%d')) t2
  on t1.day=t2.day;"

